USE HierarchyIdDemo
GO

-- CREATE new stored PROCEDURE for adding department
CREATE PROCEDURE HierarchyId.AddDepartment
	@DepartmentID INT, 
	@DepartmentName VARCHAR(100), 
	@ParentID INT
AS 
BEGIN

	DECLARE @ParentNode hierarchyid,
		@MaxChildNode hierarchyid

	BEGIN TRANSACTION

		-- get the node for the parent of the new
		-- department
		SELECT @ParentNode = Node 
			FROM HierarchyId.Org 
			WHERE DepartmentID = @ParentID

		-- get the last department for the parent
		SELECT @MaxChildNode = max(Node) 
			FROM HierarchyId.Org 
			WHERE @ParentNode = Node.GetAncestor(1)

		-- add the new department
		-- Parent.GetDescENDant(MaxChild, NULL) returns the
		-- next descENDent
		INSERT HierarchyId.Org (
				DepartmentID, 
				Node, 
				DepartmentName)
			VALUES (
				@DepartmentID, 
				@ParentNode.GetDescendant(@MaxChildNode, NULL), 
				@DepartmentName)	
	COMMIT

END 
GO

EXEC HierarchyId.AddDepartment 2, 'Operations', 1
EXEC HierarchyId.AddDepartment 3, 'Development', 1
EXEC HierarchyId.AddDepartment 4, 'Parking', 1
EXEC HierarchyId.AddDepartment 5, 'Home Operations', 2
EXEC HierarchyId.AddDepartment 6, 'Field Operations', 2
EXEC HierarchyId.AddDepartment 7, 'North Territory', 6
EXEC HierarchyId.AddDepartment 8, 'South Territory', 6
EXEC HierarchyId.AddDepartment 9,   'Database', 3
EXEC HierarchyId.AddDepartment 10,  'Services', 3
EXEC HierarchyId.AddDepartment 11,  'Applications', 3
EXEC HierarchyId.AddDepartment 12, 'Windows', 11
EXEC HierarchyId.AddDepartment 13, 'Internet', 11
EXEC HierarchyId.AddDepartment 14, 'Self', 4
EXEC HierarchyId.AddDepartment 15, 'Valet', 4
GO

-- get entire organization
SELECT DepartmentID, 
		DepartmentName, 
		Node.ToString() AS Node, 
		Level
	FROM HierarchyId.Org
	ORDER BY Node
GO